# @Time    : 2019/7/30 14:31
# @Author  : patrick
# @File    : xlsx_utils.py
# @Software: PyCharm
from openpyxl import load_workbook


class XlsxUtils:
    def __init__(self):
        """
        初始化函数
        """
        self.filename = ""
        # 载入表格数据与公式
        self.result = []

    def set_filename(self, filename):
        self.filename = filename
        self.load_workbook = load_workbook(filename=self.filename)

    def get_sheet_data(self, **args):
        """
        获取表格数据函数
        :return: 返回费率计算表格中的关键参数
        """
        # data_only意为载入纯数据，不载入公式
        self.filename = args.get("filename")
        wb = load_workbook(filename=self.filename,data_only=True)
        sheet = wb[args.get("sheetname")]
        rows = sheet.max_row
        cols = sheet.max_column
        for row in rows:
            colList = []
            for col in cols:
                colList.append(sheet.cell(row=row, column=col).value)


        # print(content)
        # for i in range(1, 9):
        #     self.result.append(sheet.cell(row=i, column=3).value)
        # wb.close()
        # for i in range(self.result.count()):
        #     print(self.result[i])
        # return self.result

    def set_sheet_value(self,**args):
        """
        自定义费率组合参数函数
        :param args: 费率计算excel表格中的键值对，例：PPP=10
        :return: 无返回
        """
        self.filename = args.get("filename")
        self.load_workbook = load_workbook(filename=self.filename)
        wb = self.load_workbook
        sheet = wb[args.get("sheetname")]
        sheet.cell(row=args.get("row"), column=args.get('col')).value = args.get("value")
        wb.save(self.filename)
        wb.close()


if __name__ == '__main__':
    test = XlsxUtils()
    test.set_sheet_value(PPP=5, Paymode="M")
